﻿/*
Copyright (C) 2009  Tomasz Chrzanowski

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program.  If not, see <http://www.gnu.org/licenses/>
*/

using System.Data.SqlClient;

namespace Com.TomCdc.ChangeDataCapture
{
    public class CdcDatabaseSetup
    {
        private string _connStr;

        public CdcDatabaseSetup(string connectionString)
        {
            _connStr = connectionString;
            SqlTemplates.Culture = System.Globalization.CultureInfo.InvariantCulture;
        }

        public void Setup(CdcDatabaseType dbType)
        {
            switch (dbType)
            {
                case CdcDatabaseType.Staging:
                    ExecuteStagingSetup();
                    break;
                case CdcDatabaseType.Storage:
                    ExecuteStorageSetup();
                    break;
                case CdcDatabaseType.Tracked:
                    ExecuteTrackedSetup();
                    break;
            }
        }

        private void ExecuteTrackedSetup()
        {
            using(SqlConnection sqlConn = new SqlConnection(_connStr))
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                sqlConn.Open();
                sqlCmd.Connection = sqlConn;
                sqlCmd.CommandType = System.Data.CommandType.Text;

                ExecuteSqlScript(SqlTemplates.CreateF_CdcIsUpdated, sqlCmd); 
            }
        }

        private void ExecuteStorageSetup()
        {
            using(SqlConnection sqlConn = new SqlConnection(_connStr))
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                sqlConn.Open();
                SqlTransaction sqlTran = sqlConn.BeginTransaction();
                sqlCmd.Connection = sqlConn;
                sqlCmd.Transaction = sqlTran;
                sqlCmd.CommandType = System.Data.CommandType.Text;

                ExecuteSqlScript(SqlTemplates.CdcStore_CreateT_ChangeTransaction, sqlCmd);
                ExecuteSqlScript(SqlTemplates.CdcStore_CreateT_SourceColumn, sqlCmd);
                ExecuteSqlScript(SqlTemplates.CdcStore_CreateT_SourceTable, sqlCmd);
                ExecuteSqlScript(SqlTemplates.CdcStore_CreateP_SelectSourceTables, sqlCmd);
                ExecuteSqlScript(SqlTemplates.CdcStore_CreateP_SelectSourceCoulumns, sqlCmd);
                ExecuteSqlScript(SqlTemplates.CdcStore_CreateP_SelectChangeHistory, sqlCmd);
                ExecuteSqlScript(SqlTemplates.CdcStore_CreateP_InsertTransactionRecord, sqlCmd);
                ExecuteSqlScript(SqlTemplates.CdcStore_CreateP_InsertSourceTable, sqlCmd);
                ExecuteSqlScript(SqlTemplates.CdcStore_CreateP_InsertSourceColumn, sqlCmd);
                ExecuteSqlScript(SqlTemplates.CdcStore_CreateF_GetCmdTypeOrder, sqlCmd);
                ExecuteSqlScript(SqlTemplates.CdcStore_AlterT_Constraints, sqlCmd);

                sqlTran.Commit();
            }
        }

        private void ExecuteStagingSetup()
        {
            using(SqlConnection sqlConn = new SqlConnection(_connStr))
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                sqlConn.Open();
                SqlTransaction sqlTran = sqlConn.BeginTransaction();
                sqlCmd.Connection = sqlConn;
                sqlCmd.Transaction = sqlTran;
                sqlCmd.CommandType = System.Data.CommandType.Text;

                ExecuteSqlScript(SqlTemplates.CdcStage_CreateT_ChangeTransaction, sqlCmd);
                ExecuteSqlScript(SqlTemplates.CdcStage_CreateP_SelectChangeTransactions, sqlCmd);
                ExecuteSqlScript(SqlTemplates.CdcStage_CreateP_DeleteProcessedTransactions, sqlCmd);
                ExecuteSqlScript(SqlTemplates.CdcStage_CreateF_IsDifferent, sqlCmd);
                ExecuteSqlScript(SqlTemplates.CdcStage_CreateF_GetChangeCommandType, sqlCmd);

                sqlTran.Commit();
            }
        }

        private void ExecuteSqlScript(string script, SqlCommand sqlCmd)
        {
            CommonHelper.ExecuteBatchArray(CommonHelper.SplitSqlBatches(script), sqlCmd);
        }
    }
}